package com.fxwater.websocket.job;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.fxwater.websocket.init.ScheduledTaskInitializer;
import com.fxwater.websocket.util.SpringBeanFinder;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.Serializable;
import java.sql.Date;
import java.sql.Timestamp;

/**
 * @Description:
 * @Author: 王明朝
 * @Date: 2019/9/23 19:27
 */
public class HistoryDataBackUpJob  implements Job {

    private static JdbcTemplate jdbcTemplate;

    private static RedisTemplate<String, Serializable> redisTemplate;

    private final String LAST_BACK_UP_KEY="LAST_BACK_UP";

    private Logger logger =  LoggerFactory.getLogger(ScheduledTaskInitializer.class);

    @Override
    public synchronized void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        logger.info("开始执行历史数据备份。");
        if(jdbcTemplate==null){
            //synchronized (jdbcTemplate){
                jdbcTemplate = SpringBeanFinder.findBean(JdbcTemplate.class);
           // }
        }
        if(redisTemplate==null){
            //synchronized (redisTemplate){
                redisTemplate = SpringBeanFinder.findBean("stringRedisTemplate");
           // }
        }
        String lastBackUpTimeStmp = null;
        if(redisTemplate.hasKey(LAST_BACK_UP_KEY)){
             lastBackUpTimeStmp = (String) redisTemplate.opsForValue().get(LAST_BACK_UP_KEY);
        }
        String condition  = "";
        Timestamp maxSqlDateTime = null;
        if(StrUtil.isEmpty(lastBackUpTimeStmp)){
            logger.info("首次进行历史数据备份。");
            //第一次数据备份
            String sqlCount = "select count(1) from fx_history_data ";
            Integer num = jdbcTemplate.queryForObject(sqlCount,Integer.class);
            logger.info("需要备份的数据记录条数是：{}。",num);
            if( num>0 ){
                String sqlSelectMinDate = "select min(lastchange) lastChange from fx_history_data ";
                Timestamp startDate = jdbcTemplate.queryForObject(sqlSelectMinDate, Timestamp.class);
                Timestamp endDate = new Timestamp(DateUtil.endOfWeek(new java.util.Date(startDate.getTime())).getTime());
                long postfix = endDate.getTime() / 1000 / 3600 / 24 / 7;
                String backTableName = "fx_history_data_" + postfix;
                String tableCheckSql ="select count(1) from sysobjects where id=object_id('"+backTableName+"') and type = 'U'";
                Integer tableNum = jdbcTemplate.queryForObject(tableCheckSql,Integer.class);
                String sql = null;
                boolean end = false;
                if(tableNum>0){
                    sql = " insert  into  "+backTableName+" (is_valid,is_deleted,item_code,item_val,lastchange,remark) select  is_valid,is_deleted,item_code,item_val,lastchange,remark from fx_history_data where  lastchange>=? and lastchange<=?";
                    logger.info("复制数据时间从{}到{}的数据到数据表{}中。",startDate,endDate,backTableName);
                    jdbcTemplate.update(sql,new Object[]{startDate,endDate});
                    String sqlMaxDate =  "select max(lastchange) lastChange from "+backTableName;
                    Timestamp maxDate = jdbcTemplate.queryForObject(sqlMaxDate, Timestamp.class);
                    if(maxDate!=null){
                        maxSqlDateTime = maxDate;
                    }
                } else {
                    sql = " select * into "+backTableName+" from  fx_history_data  where lastchange>=? and lastchange<=?";
                    jdbcTemplate.update(sql,new Object[]{startDate,endDate});
                    logger.info("备份数据时间从{}到{}的数据到数据表{}中。",startDate,endDate,backTableName);
                    String sqlMaxDate =  "select max(lastchange) lastChange from "+backTableName;
                    Timestamp maxDate = jdbcTemplate.queryForObject(sqlMaxDate, Timestamp.class);
                    if(maxDate!=null){
                        maxSqlDateTime = maxDate;
                    }
                }
                do {
                    sqlCount = "select count(1) from fx_history_data where lastchange>? ";
                    num = jdbcTemplate.queryForObject(sqlCount,new Object[]{endDate},Integer.class);
                    logger.info("数据时间大于{}的数据记录{}条。",endDate,num);
                    if(num>0){
                        startDate =  new Timestamp(endDate.getTime()+1);
                        endDate = new Timestamp(DateUtil.endOfWeek(new java.util.Date(startDate.getTime())).getTime());
                        postfix = endDate.getTime() / 1000 / 3600 / 24 / 7;
                        backTableName = "fx_history_data_" + postfix;
                        tableCheckSql ="select count(1) from sysobjects where id=object_id('"+backTableName+"') and type = 'U'";
                        tableNum = jdbcTemplate.queryForObject(tableCheckSql,Integer.class);
                        if(tableNum>0){
                            sql = " insert  into  "+backTableName+" (is_valid,is_deleted,item_code,item_val,lastchange,remark) select  is_valid,is_deleted,item_code,item_val,lastchange,remark from fx_history_data where  lastchange>=? and lastchange<=?";
                            logger.info("复制数据时间从{}到{}的数据到数据表{}中。",startDate,endDate,backTableName);
                            jdbcTemplate.update(sql,new Object[]{startDate,endDate});
                            String sqlMaxDate =  "select max(lastchange) lastChange from "+backTableName;
                            Timestamp maxDate = jdbcTemplate.queryForObject(sqlMaxDate, Timestamp.class);
                            if(maxDate!=null && maxSqlDateTime!=null && maxDate.getTime()>maxSqlDateTime.getTime()){
                                maxSqlDateTime = maxDate;
                            }
                        } else {
                            sql = "select * into " + backTableName + " from  fx_history_data where lastchange>=? and lastchange<=?";
                            logger.info("备份数据时间从{}到{}的数据到数据表{}中。",startDate,endDate,backTableName);
                            jdbcTemplate.update(sql,new Object[]{startDate,endDate});
                            String sqlMaxDate =  "select max(lastchange) lastChange from "+backTableName;
                            Timestamp maxDate = jdbcTemplate.queryForObject(sqlMaxDate, Timestamp.class);
                            if(maxDate!=null && maxSqlDateTime!=null && maxDate.getTime()>maxSqlDateTime.getTime()){
                                maxSqlDateTime = maxDate;
                            }
                        }
                    } else {
                        end = true;
                    }
                } while(!end);
                if(maxSqlDateTime!=null){
                    redisTemplate.opsForValue().set(LAST_BACK_UP_KEY,maxSqlDateTime.getTime()+"");
                    logger.info("记录最新更新数据时间。",maxSqlDateTime);
                }
                String sqlTruncate = "TRUNCATE table fx_history_data ";
                jdbcTemplate.update(sqlTruncate);
            }
        } else {
            //不是第一次数据备份
            String sqlCount = "select count(1) from fx_history_data  where lastchange>=?";
            long timeStmp = Long.parseLong(lastBackUpTimeStmp)+1;
            Timestamp startDate = new Timestamp(timeStmp);
            logger.info("缓存的上次备份数据的时间是：{}",startDate);
            Integer num = jdbcTemplate.queryForObject(sqlCount,new Object[]{startDate},Integer.class);
            if(num>0){
                Timestamp endDate = new Timestamp(DateUtil.endOfWeek(new java.util.Date(startDate.getTime())).getTime());
                long postfix = endDate.getTime() / 1000 / 3600 / 24 / 7;
                String backTableName = "fx_history_data_" + postfix;

                String tableCheckSql ="select count(1) from sysobjects where id=object_id('"+backTableName+"') and type = 'U'";
                Integer tableNum = jdbcTemplate.queryForObject(tableCheckSql,Integer.class);
                String sql = null;
                if(tableNum>0){
                    sql = " insert  into  "+backTableName+" (is_valid,is_deleted,item_code,item_val,lastchange,remark) select  is_valid,is_deleted,item_code,item_val,lastchange,remark from fx_history_data where  lastchange>=? and lastchange<=?";
                    logger.info("复制数据时间从{}到{}的数据到数据表{}中。",startDate,endDate,backTableName);
                    jdbcTemplate.update(sql,new Object[]{startDate,endDate});
                    String sqlMaxDate =  "select max(lastchange) lastChange from "+backTableName;
                    Timestamp maxDate = jdbcTemplate.queryForObject(sqlMaxDate, Timestamp.class);
                    if(maxDate!=null){
                        maxSqlDateTime = maxDate;
                    }
                } else {
                    sql = "select * into " + backTableName + " from  fx_history_data where lastchange>=? and lastchange<=?";
                    logger.info("备份数据时间从{}到{}的数据到数据表{}中。",startDate,endDate,backTableName);
                    jdbcTemplate.update(sql,new Object[]{startDate,endDate});
                    String sqlMaxDate =  "select max(lastchange) lastChange from "+backTableName;
                    Timestamp maxDate = jdbcTemplate.queryForObject(sqlMaxDate, Timestamp.class);
                    if(maxDate!=null){
                        maxSqlDateTime = maxDate;
                    }
                }
                boolean end = false;
                do {
                    sqlCount = "select count(1) from fx_history_data where lastchange>? ";
                    num = jdbcTemplate.queryForObject(sqlCount,new Object[]{endDate},Integer.class);
                    logger.info("数据时间大于{}的数据记录{}条。",endDate,num);
                    if(num>0){
                        startDate =  new Timestamp(endDate.getTime()+1);
                        endDate = new Timestamp(DateUtil.endOfWeek(new java.util.Date(startDate.getTime())).getTime());
                        postfix = endDate.getTime() / 1000 / 3600 / 24 / 7;
                        backTableName = "fx_history_data_" + postfix;
                        tableCheckSql ="select count(1) from sysobjects where id=object_id('"+backTableName+"') and type = 'U'";
                        tableNum = jdbcTemplate.queryForObject(tableCheckSql,Integer.class);
                        if(tableNum>0){
                            sql = " insert  into  "+backTableName+" (is_valid,is_deleted,item_code,item_val,lastchange,remark) select  is_valid,is_deleted,item_code,item_val,lastchange,remark from fx_history_data where  lastchange>=? and lastchange<=?";
                            logger.info("复制数据时间从{}到{}的数据到数据表{}中。",startDate,endDate,backTableName);
                            jdbcTemplate.update(sql,new Object[]{startDate,endDate});
                            String sqlMaxDate =  "select max(lastchange) lastChange from "+backTableName;
                            Timestamp maxDate = jdbcTemplate.queryForObject(sqlMaxDate, Timestamp.class);
                            if(maxDate!=null && maxSqlDateTime!=null && maxDate.getTime()>maxSqlDateTime.getTime()){
                                maxSqlDateTime = maxDate;
                            }
                        } else {
                            sql = "  select * into "+backTableName+" from  fx_history_data  where lastchange>=? and lastchange<=?";
                            logger.info("备份数据时间从{}到{}的数据到数据表{}中。",startDate,endDate,backTableName);
                            jdbcTemplate.update(sql,new Object[]{startDate,endDate});
                            String sqlMaxDate =  "select max(lastchange) lastChange from "+backTableName;
                            Timestamp maxDate = jdbcTemplate.queryForObject(sqlMaxDate, Timestamp.class);
                            if(maxDate!=null && maxSqlDateTime!=null && maxDate.getTime()>maxSqlDateTime.getTime()){
                                maxSqlDateTime = maxDate;
                            }
                        }
                    } else {
                        end = true;
                    }
                } while(!end);
                if(maxSqlDateTime!=null){
                    redisTemplate.opsForValue().set(LAST_BACK_UP_KEY,maxSqlDateTime.getTime()+"");
                    logger.info("记录最新更新数据时间：{}。",maxSqlDateTime);
                }
                String sqlTruncate = "TRUNCATE table fx_history_data ";
                jdbcTemplate.update(sqlTruncate);
                logger.info("结束执行历史数据备份。");
            }
        }



    }
}
